﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Cotizav.CONEXION;
using System.Data.Linq.Mapping;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Data;

namespace Cotizav.CONTROLADOR
{
    class CtrlImportar
    {
        private ConexionDataContext conexion;

        private CtrlCargo ctCargo;
        private CtrlClientes ctCliente;

        public CtrlImportar()
        {
            ctCargo = new CtrlCargo();
            ctCliente = new CtrlClientes();
        }

        internal List<string> obtenerNombreTablas()
        {
            using (conexion = new ConexionDataContext())
            {
                List<string> result = new List<string>();
                var model = new AttributeMappingSource().GetModel(typeof(ConexionDataContext));

                var orderedModel = model.GetTables().OrderBy(x => x.TableName);

                foreach (var c in orderedModel)
                {
                    result.Add(c.TableName);
                }

                return result;
            }
        }

        internal Dictionary<int,string> obtenerListaAtributos(string nombreObjeto)
        {
            using(conexion = new ConexionDataContext())
            {
                Dictionary<int,string> result = new Dictionary<int,string>();

                var model = new AttributeMappingSource().GetModel(typeof(ConexionDataContext));

                var mt = model.GetTables().SingleOrDefault(x => x.TableName == nombreObjeto);

                int i = 0;

                foreach (var dm in mt.RowType.DataMembers)
                {
                    if (!dm.IsPrimaryKey && 
                        !dm.IsAssociation && 
                        !dm.Name.StartsWith("id") && 
                        !dm.Name.Contains("eliminado") && 
                        !dm.Name.Contains("fechaRegistro") &&
                        !dm.Name.Contains("fechaModificacion"))
                    {
                        result.Add(i, dm.Name);                        
                    }
                    i++;                   
                }

                return result;
            }
        }

        internal System.Data.DataTable importarArchivo(string rutaArchivo, bool tieneEncabezado)
        {
            DataTable result = new DataTable();
            
            Excel.Application appExl;
            Excel.Workbook workbook;
            Excel.Worksheet newSheet;
            Excel.Range shtRange;

            appExl = new Microsoft.Office.Interop.Excel.Application();

            workbook = appExl.Workbooks.Open(rutaArchivo, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            newSheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);

            int ColNum = 0;
            int RowNum = 0;

            shtRange = newSheet.UsedRange;
            
            //creamos el datatable con las columnas correspondientes

            if (tieneEncabezado)
            {
                for (int c = 0; c < shtRange.Columns.Count; c++)
                {
                    string columnName = (shtRange.Cells[1, c + 1] as Excel.Range).Value2 != null ? (shtRange.Cells[1, c + 1] as Excel.Range).Value2.ToString() : string.Empty;
                    result.Columns.Add(new DataColumn(columnName,Type.GetType("System.String")));
                }

                for (RowNum = 2; RowNum <= shtRange.Rows.Count; RowNum++)
                {
                    DataRow dr = result.NewRow();
                    for (ColNum = 1; ColNum <= shtRange.Columns.Count; ColNum++)
                    {
                        dr[ColNum - 1] = (shtRange.Cells[RowNum, ColNum] as Excel.Range).Value2 != null ? (shtRange.Cells[RowNum, ColNum] as Excel.Range).Value2.ToString() : string.Empty;
                    }

                    result.Rows.Add(dr);
                    result.AcceptChanges();
                }

            }
            else
            {
                for (int c = 0; c < shtRange.Columns.Count; c++)
                {
                    result.Columns.Add(new DataColumn("Columna " + (c + 1), Type.GetType("System.String")));
                }

                for (RowNum = 1; RowNum <= shtRange.Rows.Count; RowNum++)
                {
                    DataRow dr = result.NewRow();
                    for (ColNum = 1; ColNum <= shtRange.Columns.Count; ColNum++)
                    {
                        dr[ColNum - 1] = (shtRange.Cells[RowNum, ColNum] as Excel.Range).Value2 != null ? (shtRange.Cells[RowNum, ColNum] as Excel.Range).Value2.ToString() : string.Empty;
                    }

                    result.Rows.Add(dr);
                    result.AcceptChanges();
                }

            }

            workbook.Close(true, Missing.Value, Missing.Value);
            appExl.Quit();            

            return result;
        }

        internal void cargarInformacion(string nombreClase, DataTable dtInformacion)
        {
            using (conexion = new ConexionDataContext())
            {
                switch (nombreClase)
                {
                    case "dbo.CARACTERISTICA":
                        break;                        
                    case "dbo.CARGO":
                        CARGO newCargo;

                        foreach (DataRow dr in dtInformacion.Rows)
                        {
                            newCargo = new CARGO();

                            for (int i = 0; i < dr.ItemArray.Count(); i++)
                            {
                                switch (i)
                                {
                                    case 0:
                                        newCargo.nombreCargo = dr[0].ToString();
                                        break;
                                }
                            }

                            //registrar el objeto
                            ctCargo.registrarCargo(newCargo);
                        }
                        break;
                    case "dbo.CARTA_COMPROMISO":
                        break;
                    case "dbo.CARTACOMPROMISO_DETALLE":
                        break;
                    case "dbo.CATEGORIA":
                        break;
                    case "dbo.CLIENTE":
                        CLIENTE newCliente;

                        foreach (DataRow dr in dtInformacion.Rows)
                        {
                            newCliente = new CLIENTE();

                            for (int i = 0; i < dr.ItemArray.Count(); i++)
                            {
                                switch (i)
                                {
                                    case 0:
                                        newCliente.direccion = dr[0].ToString();
                                        break;
                                    case 1:
                                        newCliente.provincia = dr[1].ToString();
                                        break;
                                    case 2:
                                        newCliente.email = dr[2].ToString();
                                        break;
                                    case 3:
                                        newCliente.razonSocial = dr[3].ToString();
                                        break;
                                    case 4:
                                        newCliente.clienteruc = dr[4].ToString();
                                        break;
                                    case 5:
                                        newCliente.telefono = dr[5].ToString();
                                        break;
                                }
                            }

                            ctCliente.registrarCliente(newCliente);
                        }
                        break;
                    case "dbo.CONDICION":
                        break;
                    case "dbo.CONDICION_COTIZACION":
                        break;
                    case "dbo.CONTACTO":
                        break;
                    case "dbo.CONTACTO_COTIZACION":
                        break;
                    case "dbo.COTIZACION":
                        break;
                    case "dbo.COTIZACION_DETALLE":
                        break;
                    case "dbo.COTIZAV_CORRELATIVO":
                        break;
                    case "dbo.DISTRITO":
                        break;
                    case "dbo.EQUIPO":
                        break;
                    case "dbo.FAMILIA":
                        break;
                    case "dbo.FAMILIAXMARCA":
                        break;
                    case "dbo.FORMATO":
                        break;
                    case "dbo.FORMATO_COTIZACION":
                        break;
                    case "dbo.HORA_TARIFA":
                        break;
                    case "dbo.LISTA_TARIFA":
                        break;
                    case "dbo.MARCA":
                        break;
                    case "dbo.MODELO":
                        break;
                    case "dbo.MOTIVO":
                        break;
                    case "dbo.PERIODO":
                        break;
                    case "dbo.PROVINCIA":
                        break;
                    case "dbo.SECTOR":
                        break;
                    case "dbo.SUCURSAL":
                        break;
                    case "dbo.TIPO_CAMBIO":
                        break;
                    case "dbo.VENDEDOR":
                        break;
                    case "dbo.VISITA":
                        break;
                } 
            }
        }

        internal void moverColumnas(DataTable dtInformacion, int indiceActual, int moveraIndice)
        {
            
        }
    }
}
